package com.gem.growingtree.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.gem.growingtree.db.DBLink;
import com.gem.growingtree.model.PageBean4;
import com.gem.growingtree.model.StudentsAccount;

public class StudentsAccountService {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	public void close(){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		if(pstmt != null){
			try {
				pstmt.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
	}


	public PageBean4 findByPage(int pageCode, int pageSize,String classId) throws Exception {
		if(pageCode<=0)
		{
			pageCode=1;
		}
		PageBean4 pb = new PageBean4();
		StudentsAccount studentsAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(babyId) from baby where classId in(" + classId + ")";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);

		sql = "SELECT babyId, babyName, babySex,babyAge,babyBirthday,className,classes.schoolName FROM baby,classes " +
				"WHERE classes.classId = baby.classId limit ?,? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, (pageCode-1)*pageSize);
		pstmt.setInt(2, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String babyId = rs.getString(1);
			String babyName = rs.getString(2);
			String babySex = rs.getString(3);
			String babyAge = rs.getString(4);
			String babyBirthday = rs.getString(5);
			String className = rs.getString(6);
			String schoolName = rs.getString(7);
			studentsAccount = new StudentsAccount(babyId,babyName,babySex,babyAge,babyBirthday,className,schoolName);
			pb.getList().add(studentsAccount);
		}
		return pb;
	}

	public PageBean4 findByPage2(int pageCode, int pageSize,String babyId) throws Exception {
		PageBean4 pb = new PageBean4();
		StudentsAccount studentsAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(babyId) from baby where babyId in(" + babyId + ")";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);

		sql = "SELECT babyId, babyName, babySex,babyAge,babyBirthday,className,classes.schoolName FROM baby,classes " +
				"WHERE classes.classId = baby.classId and babyId=? limit ?,? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, babyId);
		pstmt.setInt(2, (pageCode-1)*pageSize);
		pstmt.setInt(3, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String babyId1 = rs.getString(1);
			String babyName = rs.getString(2);
			String babySex = rs.getString(3);
			String babyAge = rs.getString(4);
			String babyBirthday = rs.getString(5);
			String className = rs.getString(6);
			String schoolName = rs.getString(7);
			studentsAccount = new StudentsAccount(babyId1,babyName,babySex,babyAge,babyBirthday,className,schoolName);
			pb.getList().add(studentsAccount);
		}
		return pb;
	}
	// 查找某个班级全部学生信息
	public PageBean4 findByPage3(int pageCode, int pageSize,String classId) throws Exception {
		PageBean4 pb = new PageBean4();
		StudentsAccount studentsAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(babyId) from baby where classId in(" + classId + ")";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);
		sql = "SELECT babyId, babyName, babySex,babyAge,babyBirthday,className,classes.schoolName FROM baby,classes " +
				"WHERE classes.classId = baby.classId and classes.classId=? limit ?,? ";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, classId);
		pstmt.setInt(2, (pageCode-1)*pageSize);
		pstmt.setInt(3, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String babyId1 = rs.getString(1);
			String babyName = rs.getString(2);
			String babySex = rs.getString(3);
			String babyAge = rs.getString(4);
			String babyBirthday = rs.getString(5);
			String className = rs.getString(6);
			String schoolName = rs.getString(7);
			studentsAccount = new StudentsAccount(babyId1,babyName,babySex,babyAge,babyBirthday,className,schoolName);
			pb.getList().add(studentsAccount);
		}
		return pb;
	}
	// 修改某个学生信息
	public boolean updateStudent(String babyId,StudentsAccount StudentsAccount)
			throws SQLException {
		boolean flag = false;
		conn = DBLink.getConnection();
		String sql = "update baby set babyName=?,babySex=?,babyAge=?,babyBirthday=?,className=?,schoolName where babyId in(" + babyId + ")";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, StudentsAccount.getBabyName());
		pstmt.setString(2, StudentsAccount.getBabySex());
		pstmt.setString(3, StudentsAccount.getBabyAge());
		pstmt.setString(4, StudentsAccount.getBabyBirthday());
		pstmt.setString(5, StudentsAccount.getClassName());
		pstmt.setString(6, StudentsAccount.getSchoolName());
		int rows = pstmt.executeUpdate(sql);
		if (rows == 1) {
			flag = true;
		}
		return flag;
	}
	//删除某个学生的信息
	public boolean deleteStudentInfo(String babyId ) {
		boolean flag = false;
		conn = DBLink.getConnection();
		String sql = "delete from baby where babyId in(" + babyId + ")";
		try {
			pstmt = conn.prepareStatement(sql);
			int rows = pstmt.executeUpdate(sql);
			if (rows > 0)
			{
				flag = true;
				System.out.println(flag);
				System.out.println("学生信息删除成功");
			} else {
				System.out.println("删除失败");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}	
}

